package com.liveHere.www.dao;

import com.liveHere.www.Exception.ShopDaoException;
import com.liveHere.www.po.Shop;
import com.liveHere.www.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;


/**
 * @author Mo312
 */
public class ShopDaoImpl implements ShopDao {

    //商家注册
    @Override
    public boolean register(Shop shop) throws Exception {
        String sql = "insert into shops(shop_name,shop_password,address,contact,keywords,date) values(?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, shop.getShopName());
            pstmt.setString(2, shop.getShopPassword());
            pstmt.setString(3, shop.getShopAddress());
            pstmt.setString(4, shop.getShopContact());
            pstmt.setString(5, shop.getKeywords());
            pstmt.setDate(6, Date.valueOf(shop.getDate()));

            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            throw new ShopDaoException("商家注册失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, null);
        }
    }

    //商家登录
    @Override
    public Shop login(String shopName, String shopPassword) throws Exception {
        String sql = "select * from shops where shop_name=? and shop_password=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, shopName);
            pstmt.setString(2, shopPassword);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                Shop shop = new Shop();
                shop.setShopId(rs.getInt("shop_id"));
                shop.setShopName(rs.getString("shop_name"));
                shop.setShopPassword(rs.getString("shop_password"));
                shop.setShopAddress(rs.getString("address"));
                shop.setShopContact(rs.getString("contact"));
                shop.setKeywords(rs.getString("keywords"));
                shop.setStatus(rs.getString("status"));
                shop.setApplyStatus(rs.getString("apply_status"));
                shop.setDate(rs.getDate("date").toLocalDate());
                shop.setIncome(rs.getDouble("income"));
                return shop;
            }
        } catch (Exception e) {
            throw new ShopDaoException("商家登录失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, rs);
        }
        return null;
    }

    //更新店铺信息
    @Override
    public boolean updateShopInfo(Shop shop) throws Exception {
        String sql = "update shops set shop_name=?, address=?, contact=?, keywords=? where shop_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, shop.getShopName());
            pstmt.setString(2, shop.getShopAddress());
            pstmt.setString(3, shop.getShopContact());
            pstmt.setString(4, shop.getKeywords());
            pstmt.setInt(5, shop.getShopId());
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            throw new ShopDaoException("更新店铺信息失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, null);
        }
    }

    //查询商铺列表
    @Override
    public ArrayList<Shop> getAllShops() throws Exception {
        String sql = "SELECT * FROM shops";
        ArrayList<Shop> shops = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Shop shop = new Shop();
                shop.setShopId(rs.getInt("shop_id"));
                shop.setShopName(rs.getString("shop_name"));
                shop.setShopAddress(rs.getString("address"));
                shop.setShopContact(rs.getString("contact"));
                shop.setKeywords(rs.getString("keywords"));
                shop.setStatus(rs.getString("status"));
                shop.setApplyStatus(rs.getString("apply_status"));
                shop.setDate(rs.getDate("date").toLocalDate());
                shop.setIncome(rs.getDouble("income"));
                shops.add(shop);
            }
        } catch (Exception e) {
            throw new ShopDaoException("查询商铺列表失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, rs);
        }
        return shops;
    }

    //更新店铺收入
    @Override
    public void updateShopIncome(int shopId, double income) throws Exception {
        String sql = "UPDATE shops SET income = income + ? WHERE shop_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, income);
            pstmt.setInt(2, shopId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new ShopDaoException("更新店铺收入失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, null);
        }
    }

    //计算营业额
    @Override
    public double calculateIncome(int shopId) throws Exception {
        String sql = "SELECT income FROM shops WHERE shop_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, shopId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getDouble("income");
            } else {
                return 0.0;
            }
        } catch (SQLException e) {
            throw new ShopDaoException("计算营业额失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, rs);
        }
    }

    //获取待审核商铺
    @Override
    public ArrayList<Shop> getPendingShops() throws SQLException {
        String sql = "SELECT * FROM shops WHERE apply_status = 'pending'";
        ArrayList<Shop> shops = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Shop shop = new Shop();
                shop.setShopId(rs.getInt("shop_id"));
                shop.setShopName(rs.getString("shop_name"));
                shop.setShopAddress(rs.getString("address"));
                shop.setShopContact(rs.getString("contact"));
                shop.setKeywords(rs.getString("keywords"));
                shop.setStatus(rs.getString("status"));
                shop.setApplyStatus(rs.getString("apply_status"));
                shop.setDate(rs.getDate("date").toLocalDate());
                shop.setIncome(rs.getDouble("income"));
                shops.add(shop);
            }
        } catch (Exception e) {
            throw new ShopDaoException("获取待审核商铺失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, rs);
        }
        return shops;
    }

    // 审核通过店铺入驻申请
    @Override
    public boolean approveShop(int shopId) throws SQLException {
        String sql = "UPDATE shops SET apply_status = 'approved' WHERE shop_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, shopId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            throw new ShopDaoException("审核通过店铺入驻申请失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, null);
        }
    }

    // 拒绝店铺入驻申请
    @Override
    public boolean rejectShop(int shopId) throws SQLException {
        String sql = "UPDATE shops SET apply_status = 'rejected' WHERE shop_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, shopId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            throw new ShopDaoException("拒绝店铺入驻申请失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, null);
        }
    }

    // 封禁店铺
    @Override
    public boolean banShop(int shopId) throws SQLException {
        String sql = "UPDATE shops SET status = 'banned' WHERE shop_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, shopId);
            return pstmt.executeUpdate() > 0;
        } catch (Exception e) {
            throw new ShopDaoException("封禁店铺失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, null);
        }
    }

    // 按日期排序获取所有商铺
    @Override
    public ArrayList<Shop> getAllShopsOrderByDate() throws SQLException {
        ArrayList<Shop> shops = new ArrayList<>();
        String sql = "SELECT * FROM shops ORDER BY date DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Shop shop = new Shop();
                shop.setShopId(rs.getInt("shop_id"));
                shop.setShopName(rs.getString("shop_name"));
                shop.setShopPassword(rs.getString("shop_password"));
                shop.setShopAddress(rs.getString("address"));
                shop.setShopContact(rs.getString("contact"));
                shop.setKeywords(rs.getString("keywords"));
                shop.setStatus(rs.getString("status"));
                shop.setApplyStatus(rs.getString("apply_status"));
                shop.setDate(rs.getDate("date").toLocalDate());
                shop.setIncome(rs.getDouble("income"));
                shops.add(shop);
            }
        } catch (SQLException e) {
            throw new ShopDaoException("按日期排序获取所有商铺失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, rs);
        }
        return shops;
    }

    // 按销量排序获取所有商铺
    @Override
    public ArrayList<Shop> getAllShopsOrderBySales() throws SQLException {
        ArrayList<Shop> shops = new ArrayList<>();
        //多表查询，获取店铺的销量
        String sql = "SELECT * FROM shops s " +
                "JOIN (SELECT shop_id, COUNT(*) AS sales FROM `orders` GROUP BY shop_id) o " +
                "ON s.shop_id = o.shop_id " +
                "ORDER BY o.sales DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Shop shop = new Shop();
                shop.setShopId(rs.getInt("shop_id"));
                shop.setShopName(rs.getString("shop_name"));
                shop.setShopAddress(rs.getString("address"));
                shop.setShopContact(rs.getString("contact"));
                shop.setKeywords(rs.getString("keywords"));
                shop.setStatus(rs.getString("status"));
                shop.setApplyStatus(rs.getString("apply_status"));
                shop.setDate(rs.getDate("date").toLocalDate());
                shop.setIncome(rs.getDouble("income"));
                shops.add(shop);
            }
        } catch (SQLException e) {
            throw new ShopDaoException("按销量排序获取所有商铺失败" + e.getMessage(), e);
        } finally {
            JDBCUtils.close(conn, pstmt, rs);
        }
        return shops;
    }
}
